package com.zaoke.web.dao.zone.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.zaoke.web.bo.zone.Zone;
import com.zaoke.web.dao.JdbcSupport;
import com.zaoke.web.dao.zone.ZoneDao;

public class ZoneDaoImpl extends JdbcSupport implements ZoneDao {

	private static final String TABLE = "`zaoke`.`zone`";
	private static final String QUERY_VIEW = "`id`, `name`, `type`, `parent_id`, `state`, `sequence`, `pick_start_time`, `pick_end_time`, `add_userid`, `add_time`, `lastmod_userid`,`lastmod_time`";

	public Set<Zone> getZonesByState(Integer[] states) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE
				+ " where `state` in (";
		sql += StringUtils.join(states, ",");
		sql += ")";

		Set<Zone> result = new HashSet<Zone>();
		List<Zone> zones = getTemplateMgr().getReadTp().query(sql,
				new RowMapper<Zone>() {

					public Zone mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Zone zone = new Zone();
						zone.setId(rs.getLong("id"));
						zone.setName(rs.getString("name"));
						zone.setType(rs.getInt("type"));
						zone.setState(rs.getInt("state"));
						zone.setSequence(rs.getInt("sequence"));
						zone.setPickStartTime(rs.getString("pick_start_time"));
						zone.setPickEndTime(rs.getString("pick_end_time"));
						zone.setAddUserId(rs.getLong("add_userid"));
						zone.setAddTime(rs.getDate("add_time"));
						zone.setLastmodUserId(rs.getLong("lastmod_userid"));
						zone.setLastmodTime(rs.getDate("lastmod_time"));
						zone.setParentId(rs.getInt("parent_id"));
						return zone;
					}
				});

		if (zones != null && zones.size() > 0)
			result.addAll(zones);
		return result;
	}
	
	public List<Zone> findZoneByIds(List<Long> ids) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE
				+ " where `id` in (";
		sql += StringUtils.join(ids, ",");
		sql += ")";
		
		List<Zone> result = new ArrayList<Zone>();
		List<Zone> zones = getTemplateMgr().getReadTp().query(sql,
				new RowMapper<Zone>() {

					public Zone mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Zone zone = new Zone();
						zone.setId(rs.getLong("id"));
						zone.setName(rs.getString("name"));
						zone.setType(rs.getInt("type"));
						zone.setState(rs.getInt("state"));
						zone.setSequence(rs.getInt("sequence"));
						zone.setPickStartTime(rs.getString("pick_start_time"));
						zone.setPickEndTime(rs.getString("pick_end_time"));
						zone.setAddUserId(rs.getLong("add_userid"));
						zone.setAddTime(rs.getDate("add_time"));
						zone.setLastmodUserId(rs.getLong("lastmod_userid"));
						zone.setLastmodTime(rs.getDate("lastmod_time"));
						zone.setParentId(rs.getInt("parent_id"));
						return zone;
					}
				});

		if (zones != null && zones.size() > 0)
			result.addAll(zones);
		return result;
	}
	public List<Zone> findZoneByParentIds(List<Long> ids) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE
				+ " where `parent_id` in (";
		sql += StringUtils.join(ids, ",");
		sql += ")";

		List<Zone> result = new ArrayList<Zone>();
		List<Zone> zones = getTemplateMgr().getReadTp().query(sql,
				new RowMapper<Zone>() {

					public Zone mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Zone zone = new Zone();
						zone.setId(rs.getLong("id"));
						zone.setName(rs.getString("name"));
						zone.setType(rs.getInt("type"));
						zone.setState(rs.getInt("state"));
						zone.setSequence(rs.getInt("sequence"));
						zone.setPickStartTime(rs.getString("pick_start_time"));
						zone.setPickEndTime(rs.getString("pick_end_time"));
						zone.setAddUserId(rs.getLong("add_userid"));
						zone.setAddTime(rs.getDate("add_time"));
						zone.setLastmodUserId(rs.getLong("lastmod_userid"));
						zone.setLastmodTime(rs.getDate("lastmod_time"));
						zone.setParentId(rs.getInt("parent_id"));
						return zone;
					}
				});

		if (zones != null && zones.size() > 0)
			result.addAll(zones);
		return result;
	}
	

	@Override
	public boolean newZone(Zone zone) {

		if(zone==null)
		{
			return false;
		}
		String sql = "insert into zaoke.zone(name,type,parent_id,state,pick_start_time,pick_end_time,add_userid,add_time,lastmod_userid,lastmod_time) values(?,?,?,?,?,?,?,now(),?,now())";
		Object[] params = new Object[]{zone.getName(),zone.getType(),zone.getParentId(),Zone.STATE_ACTIVE,zone.getPickStartTime(),zone.getPickEndTime(),zone.getAddUserId(),zone.getAddUserId()};
		return getTemplateMgr().getWriteTp().update(sql, params)>0;
	}

	
	
	@Override
	public boolean updateZone(Zone zone) {
		
		if(zone!=null)
		{
			
			String sql = "update zaoke.zone set lastmod_time=now() ";
			
			List<Object> params = new LinkedList<Object>();
			
			String name = zone.getName();
			Integer parentid = zone.getParentId();
			String pickstart = zone.getPickStartTime();
			String pickend = zone.getPickEndTime();
			Integer state = zone.getState();
			Long moduserid = zone.getLastmodUserId();
			
			if(name!=null && !"".equals(name))
			{
				sql += " ,`name`=? ";
				params.add(name);
			}
			if(parentid!=null)
			{
				sql += " ,parent_id=? ";
				params.add(parentid);
			}
			if(pickstart!=null && !"".equals(pickstart))
			{
				sql += " ,pick_start_time=? ";
				params.add(pickstart);
			}
			if(pickend!=null && !"".equals(pickend))
			{
				sql += " ,pick_end_time=? ";
				params.add(pickend);
			}
			if(moduserid!=null)
			{
				sql += " ,lastmod_userid=? ";
				params.add(moduserid);
			}
			if(zone.getSequence()!=null)
			{
				sql += " ,sequence=? ";
				params.add(zone.getSequence());
			}
			if(state!=null)
			{
				sql += " ,state=? ";
				params.add(state);
			}
			sql += " where id=?";
			params.add(zone.getId());
			getTemplateMgr().getWriteTp().update(sql, params.toArray());
			return true;
		}
		return false;
	}

	@Override
	public boolean deleteZone(List<Long> zoneid) {
		if(zoneid==null || zoneid.size()==0)
		{
			return false;
		}
		String sql = "update zaoke.zone set state=? where id in (";
		sql += StringUtils.join(zoneid, ",");
		sql += ")";
		Object[] params = new Object[]{Zone.STATE_INACTIVE};
		return getTemplateMgr().getWriteTp().update(sql, params)==zoneid.size();
	}

	@Override
	public List<Zone> findZonesByType(int type,boolean containsDel) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE;
		Object[] objects;
		if(containsDel)
		{
			sql += " where type=?";
			objects = new Object[]{type};
		}
		else
		{
			sql += " where type=? and state=?";
			objects = new Object[]{type,Zone.STATE_ACTIVE};
		}
		
		List<Zone> zones = getTemplateMgr().getReadTp().query(sql,
				new RowMapper<Zone>() {

					public Zone mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Zone zone = new Zone();
						zone.setId(rs.getLong("id"));
						zone.setName(rs.getString("name"));
						zone.setType(rs.getInt("type"));
						zone.setState(rs.getInt("state"));
						zone.setSequence(rs.getInt("sequence"));
						zone.setPickStartTime(rs.getString("pick_start_time"));
						zone.setPickEndTime(rs.getString("pick_end_time"));
						zone.setAddUserId(rs.getLong("add_userid"));
						zone.setAddTime(rs.getDate("add_time"));
						zone.setLastmodUserId(rs.getLong("lastmod_userid"));
						zone.setLastmodTime(rs.getDate("lastmod_time"));
						zone.setParentId(rs.getInt("parent_id"));
						return zone;
					}
				},objects);
		return zones;
		
	}

}
